package br.fatec.model.dao;

import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.List;

import br.fatec.model.entity.Formula;
import br.fatec.util.Globals;
import br.fatec.util.Persistencia;
import br.fatec.util.Validacoes;

public class FormulaDao {

	private Persistencia persis;		
	private Persistencia persisThread;
	
	public FormulaDao()
	{
		Globals globals = Globals.getInstance();
		
		this.persis = globals.getPersistencia();
		this.persisThread = globals.getThreadPersistencia();
	}
	
	public void cadastrar(Formula formula) throws Exception
	{	
		persis.cadastrar(formula);	
	}
	
	public void alterar(Formula formula) throws Exception
	{	
		persis.alterar(formula);
	}
	
	public void integrar(Formula formula) throws Exception
	{	
		persisThread.alterar(formula);
	}
	
	public void excluir(Formula formula) throws Exception
	{
		persisThread.excluir(formula.getClass(), formula.getCodigo());
	}
	
	public void desativar(Formula formula) throws Exception {
		formula = consultar(formula.getCodigo());
		
		formula.setAtivo(false);
		
		persis.alterar(formula);
	}
	
	public Formula consultar(Integer codigo) throws Exception
	{	
		String query = "select * from formula where codigo = :codigo and ativo = :ativo"; 
		
		HashMap<String, Object> parametros = new HashMap<String, Object>();
		parametros.put("codigo", codigo);
		parametros.put("ativo", true);
		
		return (Formula)persis.consultar(Formula.class, query, parametros);
	}
	
	@SuppressWarnings("unchecked")
	public List<Formula> listar() throws Exception
	{
		String query = "select * from formula where ativo = :ativo order by codigo asc";
		
		HashMap<String, Object> parametros = new HashMap<String, Object>();
		parametros.put("ativo", true);
		
		return (List<Formula>)persis.listar(Formula.class, query, parametros);
	}
	
	@SuppressWarnings("unchecked")
	public List<Formula> listar(String nome) throws Exception {
		String query = "select * from formula where nome ilike :nome and ativo = :ativo order by codigo asc";
		
		HashMap<String, Object> parametros = new HashMap<String, Object>();
		parametros.put("nome", "%" +nome+"%");
		parametros.put("ativo", true);

		return (List<Formula>) persis.listar(Formula.class, query, parametros);
	}
	
	@SuppressWarnings("unchecked")
	public List<Formula> listar(Formula.Status status) throws Exception {
		String query = "select * from formula where ativo = :ativo and status = :status order by formula.codigo";
		
		HashMap<String, Object> parametros = new HashMap<String, Object>();
		parametros.put("status", status.ordinal());
		parametros.put("ativo", true);

		return (List<Formula>) persis.listar(Formula.class, query, parametros);
	}
	
	@SuppressWarnings("unchecked")
	public List<Formula> listarIntegrar() throws Exception {
		String query = "select * from formula where integrado = :integrado order by formula.codigo";
		
		HashMap<String, Object> parametros = new HashMap<String, Object>();
		parametros.put("integrado", false);

		return (List<Formula>) persisThread.listar(Formula.class, query, parametros);
	}
	
	@SuppressWarnings("unchecked")
	public List<Formula> filtrar(String codigo, String cliente, String nome, Formula.Tipo tipo, Formula.UnidadeMedida unidadeMedida, String medico, String formulaPadrao, String valor, Formula.Status status) throws Exception 
	{
		String query = "select * from formula";
		query += " inner join prescritor on prescritor.codigo = formula.prescritor";
		query += " inner join formula_padrao on formula_padrao.codigo = formula.formula_padrao";
		query += " inner join venda on venda.codigo = formula.venda";
		query += " inner join cliente on venda.cliente = cliente.codigo";
		query += " where formula.ativo = :ativo";
		
		HashMap<String, Object> parametros = new HashMap<String, Object>();
		parametros.put("ativo", true);

		if (codigo != null && !codigo.isEmpty())
		{
			query += " and cast(formula.codigo as varchar(50)) ilike :codigo";
			parametros.put("codigo", "%" + codigo +"%");
		}
		
		if (cliente != null && !cliente.isEmpty())
		{
			query += " and cliente.nome ilike :cliente";
			parametros.put("cliente", "%" +cliente+"%");
		}

		if (nome != null && !nome.isEmpty())
		{
			query += " and formula.nome ilike :nome";
			parametros.put("nome", "%" +nome+"%");
		}
		
		if (tipo != null)
		{
			query += " and formula.tipo = :tipo";
			parametros.put("tipo", tipo.ordinal());
		}
		
		if (unidadeMedida != null)
		{
			query += " and formula.unidade_medida = :unidade_medida";
			parametros.put("unidade_medida", unidadeMedida.ordinal());
		}
		
		if (medico != null && !medico.isEmpty())
		{
			query += " and prescritor.nome ilike :medico";
			parametros.put("medico", "%" +medico+"%");
		}
		
		if (formulaPadrao != null && !formulaPadrao.isEmpty())
		{
			query += " and formula_padrao.nome ilike :formula_padrao";
			parametros.put("formula_padrao", "%" +formulaPadrao+"%");
		}
		
		if (tipo != null)
		{
			query += " and formula.tipo = :tipo";
			parametros.put("tipo", tipo.ordinal());	
		}
		
		if (valor != null && !valor.isEmpty())
		{
			if(Validacoes.isNumeric(valor.replace(",", ".")))
			{
				valor = new DecimalFormat("0.00").format(Double.parseDouble(valor.replace(",", ".")));
			}
			
			query += " and cast(formula.valor_total as varchar(50)) ilike :valor_total";
			parametros.put("valor_total", "%" +valor.replace(",", ".")+"%");
		}
		
		if (status != null)
		{
			query += " and formula.status = :status";
			parametros.put("status", status.ordinal());
		}
		
		query += " order by formula.codigo";
		
		return (List<Formula>) persis.listar(Formula.class, query, parametros);
	}
}
